package com.haier.spark.project.jdbc

import java.sql.{Connection, PreparedStatement, ResultSet}

import com.alibaba.druid.pool.DruidDataSourceFactory
import com.haier.spark.project.conf.ConfigurationManager
import com.haier.spark.project.constant.Constants

object JDBCHelper {
  private val properties = ConfigurationManager.getProperties(Constants.JDBC_PROPERTIES_PATH)
  private val dataSource = DruidDataSourceFactory.createDataSource(properties)

  /**
    * 获取数据库连接
    *
    * @return
    */
  def getConnection: Connection = {
    dataSource.getConnection
  }

  /**
    * 执行增删改操作
    *
    * @param sql    sql语句
    * @param params 参数列表（按顺序）
    * @return 影响的行数
    */
  def executeUpdate(sql: String, params: Array[Any]): Int = {
    var result: Int = 0
    val connection: Connection = getConnection
    val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
    fillParams(preparedStatement, params)
    result = preparedStatement.executeUpdate
    result
  }


  /**
    * 执行查询操作
    *
    * @param sql           sql语句
    * @param params        参数列表
    * @param process 处理返回结果的回调
    */
  def executeQuery(sql: String, params: Array[Any], process: ResultSet => Unit): Unit = {
    val conn: Connection = getConnection
    val preparedStatement: PreparedStatement = conn.prepareStatement(sql)
    fillParams(preparedStatement, params)
    val resultSet: ResultSet = preparedStatement.executeQuery
    process(resultSet)
  }

  /**
    * 批量操作
    *
    * @param sql
    * @param params
    * @return
    */
  def executeBatch(sql: String, params: List[Array[Any]]): Array[Int] = {
    var result: Array[Int] = null
    val connection: Connection = getConnection
    // 不自动提交
    connection.setAutoCommit(false)
    val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
    for (paramArray <- params) {
      var i: Int = 0
      for (_ <- paramArray) {
        preparedStatement.setObject(i + 1, paramArray(i))
        i += 1
      }
    }
    result = preparedStatement.executeBatch
    connection.commit()
    result
  }


  private def fillParams(preparedStatement: PreparedStatement, params: Array[Any]): Unit = {
    if (null != params && params.length > 0) {
      var i: Int = 0
      for (_ <- params) {
        preparedStatement.setObject(i + 1, params(i))
        i += 1
      }
    }
  }

}
